﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;


namespace KnowlegeAssistant
{
    static class DatabaseConnect
    {
        //private static string connectionString = "Initial Catalog = KnowledgeAssistant; Data Source = .; Integrated Security = true";
        private static string connectionString;// = "Initial Catalog = KnowledgeAssistant; Data Source = VAIO\\SQLEXPRESS; Integrated Security = true";
        private static SqlConnection connection;

        #region Methods

        public static void Open()
        {
            InitConnString();
            connection = new SqlConnection(connectionString);
            connection.Open();
        }

        private static void InitConnString()
        {
            StreamReader sr = new StreamReader(Environment.CurrentDirectory+"\\app.config");
            string line = sr.ReadToEnd();
            int start = line.IndexOf("<connectionString>") + "<connectionString>".Length;
            int end = line.IndexOf("</connectionString>");
            connectionString = line.Substring(start, end - start);
        }

        public static void Close()
        {
            connection.Close();
        }

        public static DataSet execQuery(string query)
        {
            DataSet data = new DataSet();
            SqlCommand com = new SqlCommand(query, connection);
            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);
            return data;
        }

        public static DataSet execSP(string sp_name, List<string> parameters)
        {
            DataSet data = new DataSet();

            string spparams = "";

            for (int i = 0; i < parameters.Count; i++)
            {
                spparams += "@param" + i + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);
            
            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            for (int i = 0; i < parameters.Count; i++)
            {
                com.Parameters.AddWithValue("@param" + i, parameters[i]);
            }

            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);
         
            return data;
        }

        public static DataSet execSP(string sp_name, List<string> parameters, Bitmap image)
        {
            DataSet data = new DataSet();

            string spparams = "";

            for (int i = 0; i < parameters.Count+1; i++)
            {
                spparams += "@param" + i + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);

            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            for (int i = 0; i < parameters.Count; i++)
            {
                com.Parameters.AddWithValue("@param" + i, parameters[i]);
            }

            MemoryStream ms = new MemoryStream();
            image.Save(ms,System.Drawing.Imaging.ImageFormat.Jpeg);
            ms.Close();
            com.Parameters.AddWithValue("@param" + (parameters.Count).ToString(), ms.ToArray());

            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);

            return data;
        }

        public static DataSet execReportSP(string sp_name, List<string> parameters)
        {
            DataSet data = new DataSet();

            string spparams = "";

            for (int i = 0; i < parameters.Count; i++)
            {
                spparams += parameters[i] + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);

            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);

            return data;
        }

        public static void execSPNonQuery(string sp_name, List<string> parameters)
        {
            string spparams = "";

            for (int i = 0; i < parameters.Count; i++)
            {
                spparams += "@param" + i + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);

            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            for (int i = 0; i < parameters.Count; i++)
            {
                com.Parameters.AddWithValue("@param" + i, parameters[i]);
            }

            com.ExecuteNonQuery();
            
        }

        public static DataSet execSP(string sp_name)
        {
            DataSet data = new DataSet();
            SqlCommand com = new SqlCommand("exec " + sp_name, connection);
            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);
            return data;
        }

        public static void execSP(string sp_name, DataTable table)
        {
            SqlCommand com = new SqlCommand("exec " + sp_name, connection);
            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(table);
        }

        #endregion


    }
}
